import pandas as pd
import numpy as np


def excel_to_config(excel_path, output_path='config.properties'):
    """
    将Excel数据转换为配置文件格式

    Args:
        excel_path: Excel文件路径
        output_path: 输出配置文件路径

    Returns:
        config_lines: 生成的配置行列表
    """

    try:
        # 读取Excel文件
        print("正在读取Excel文件...")
        df = pd.read_excel(excel_path)

        print(f"Excel文件形状: {df.shape}")
        print(f"列名: {list(df.columns)}")

        # 检查必要的列是否存在
        required_cols = ['学科id', '教材名称', '基础目录教材id', 'tutorial_version_id']
        missing_cols = [col for col in required_cols if col not in df.columns]

        if missing_cols:
            raise ValueError(f"Excel文件缺少必要的列: {missing_cols}")

        # 过滤tutorial_version_id为空的数据
        original_count = len(df)
        df_filtered = df[df['tutorial_version_id'].notna() & (df['tutorial_version_id'] != '')].copy()
        filtered_count = len(df_filtered)

        print(f"\n数据过滤结果:")
        print(f"原始记录数: {original_count}")
        print(f"过滤后记录数: {filtered_count}")
        print(f"过滤掉的记录数: {original_count - filtered_count}")

        if filtered_count == 0:
            print("警告: 过滤后没有有效数据!")
            return []

        # 生成配置行
        config_lines = []

        for index, row in df_filtered.iterrows():
            # 获取数据值
            subject_id = str(int(row['学科id'])) if pd.notna(row['学科id']) else ''
            base_edition_id = str(int(row['基础目录教材id'])) if pd.notna(row['基础目录教材id']) else ''
            tutorial_version_id = str(int(row['tutorial_version_id'])) if pd.notna(row['tutorial_version_id']) else ''

            # 生成三行配置
            config_lines.extend([
                f"istudy.edition.mapping-config[{index}].base-edition-id={base_edition_id}",
                f"istudy.edition.mapping-config[{index}].tutorial-version-id={tutorial_version_id}",
                f"istudy.edition.mapping-config[{index}].subject-id={subject_id}"
            ])

        # 保存到文件
        with open(output_path, 'w', encoding='utf-8') as f:
            for line in config_lines:
                f.write(line + '\n')

        print(f"\n配置文件已生成: {output_path}")
        print(f"总配置行数: {len(config_lines)}")
        print(f"配置组数: {len(config_lines) // 3}")

        # 显示前几行配置作为示例
        print(f"\n前10行配置示例:")
        for line in config_lines[:10]:
            print(line)

        if len(config_lines) > 10:
            print("...")

        return config_lines

    except Exception as e:
        print(f"处理过程中出现错误: {e}")
        return []


def excel_to_config_map(excel_path, output_path='config_map.properties'):
    """
    生成Map格式的配置：istudy.edition.mapping-config={"key":"value"}
    其中 key=基础目录教材id, value=tutorial_version_id

    Args:
        excel_path: Excel文件路径
        output_path: 输出配置文件路径

    Returns:
        config_map: 生成的映射字典
    """

    try:
        # 读取Excel文件
        print("正在读取Excel文件...")
        df = pd.read_excel(excel_path)

        # 检查必要的列
        required_cols = ['基础目录教材id', 'tutorial_version_id']
        missing_cols = [col for col in required_cols if col not in df.columns]

        if missing_cols:
            raise ValueError(f"Excel文件缺少必要的列: {missing_cols}")

        # 过滤数据 - 过滤tutorial_version_id为空的行
        df_filtered = df[df['tutorial_version_id'].notna() & (df['tutorial_version_id'] != '')].copy()

        print(f"原始记录数: {len(df)}")
        print(f"过滤后有效记录数: {len(df_filtered)}")

        if len(df_filtered) == 0:
            print("警告: 过滤后没有有效数据!")
            return {}

        # 构建映射字典
        config_map = {}
        duplicate_keys = []

        for index, row in df_filtered.iterrows():
            # 转换为字符串格式
            base_edition_id = str(int(row['基础目录教材id'])) if pd.notna(row['基础目录教材id']) else ''
            tutorial_version_id = str(int(row['tutorial_version_id'])) if pd.notna(row['tutorial_version_id']) else ''

            # 检查重复的key
            if base_edition_id in config_map:
                duplicate_keys.append(base_edition_id)
                print(f"警告: 发现重复的基础目录教材id={base_edition_id}, 将使用最新值")

            # 添加到映射
            if base_edition_id and tutorial_version_id:
                config_map[base_edition_id] = tutorial_version_id

        # 生成JSON格式的配置字符串
        import json
        config_json = json.dumps(config_map, ensure_ascii=False, separators=(',', ':'))
        config_line = f"istudy.edition.mapping-config={config_json}"

        # 保存到文件
        with open(output_path, 'w', encoding='utf-8') as f:
            f.write(config_line + '\n')

        print(f"\nMap格式配置文件已生成: {output_path}")
        print(f"映射对数: {len(config_map)}")

        if duplicate_keys:
            print(f"发现 {len(set(duplicate_keys))} 个重复的基础目录教材id")

        # 显示生成的配置
        print(f"\n生成的配置:")
        print(config_line)

        # 显示前几个映射示例
        print(f"\n映射示例 (前10个):")
        count = 0
        for key, value in config_map.items():
            if count >= 10:
                break
            print(f"  {key} -> {value}")
            count += 1

        if len(config_map) > 10:
            print(f"  ... 还有 {len(config_map) - 10} 个映射")

        return config_map

    except Exception as e:
        print(f"处理过程中出现错误: {e}")
        return {}


def analyze_mapping_data(excel_path):
    """
    分析Excel中的映射数据，检查重复和数据质量

    Args:
        excel_path: Excel文件路径
    """
    try:
        df = pd.read_excel(excel_path)

        print("=== 映射数据分析 ===")
        print(f"总记录数: {len(df)}")

        if '基础目录教材id' in df.columns and 'tutorial_version_id' in df.columns:
            # 过滤有效数据
            df_valid = df[df['tutorial_version_id'].notna() & (df['tutorial_version_id'] != '')].copy()
            print(f"有效记录数: {len(df_valid)}")

            # 检查基础目录教材id的重复情况
            base_edition_counts = df_valid['基础目录教材id'].value_counts()
            duplicates = base_edition_counts[base_edition_counts > 1]

            if len(duplicates) > 0:
                print(f"发现 {len(duplicates)} 个重复的基础目录教材id:")
                for base_id, count in duplicates.head(10).items():
                    print(f"  基础目录教材id={base_id} 重复 {count} 次")

                # 显示重复记录的详细信息
                print(f"\n重复记录详情 (前5个):")
                for base_id in duplicates.head(5).index:
                    duplicate_rows = df_valid[df_valid['基础目录教材id'] == base_id]
                    print(f"  基础目录教材id={base_id}:")
                    for _, row in duplicate_rows.iterrows():
                        print(f"    tutorial_version_id={row['tutorial_version_id']}")
            else:
                print("没有发现重复的基础目录教材id")

            # 显示数据样本
            print(f"\n数据样本:")
            sample_data = df_valid[['基础目录教材id', 'tutorial_version_id']].head(10)
            for _, row in sample_data.iterrows():
                print(f"  {row['基础目录教材id']} -> {row['tutorial_version_id']}")

    except Exception as e:
        print(f"分析过程中出现错误: {e}")


def excel_to_config_map_formatted(excel_path, output_path='config_map_formatted.properties'):
    """
    生成格式化的Map配置，带换行和缩进便于阅读

    Args:
        excel_path: Excel文件路径
        output_path: 输出配置文件路径
    """

    try:
        df = pd.read_excel(excel_path)

        # 检查必要列
        required_cols = ['基础目录教材id', 'tutorial_version_id']
        missing_cols = [col for col in required_cols if col not in df.columns]

        if missing_cols:
            raise ValueError(f"Excel文件缺少必要的列: {missing_cols}")

        # 过滤数据
        df_filtered = df[df['tutorial_version_id'].notna() & (df['tutorial_version_id'] != '')].copy()

        print(f"有效记录数: {len(df_filtered)}")

        if len(df_filtered) == 0:
            return {}

        # 构建映射
        config_map = {}
        for index, row in df_filtered.iterrows():
            base_edition_id = str(int(row['基础目录教材id'])) if pd.notna(row['基础目录教材id']) else ''
            tutorial_version_id = str(int(row['tutorial_version_id'])) if pd.notna(row['tutorial_version_id']) else ''

            if base_edition_id and tutorial_version_id:
                config_map[base_edition_id] = tutorial_version_id

        # 生成格式化的配置
        config_parts = []
        config_parts.append("istudy.edition.mapping-config={")

        items = list(config_map.items())
        for i, (key, value) in enumerate(items):
            separator = "," if i < len(items) - 1 else ""
            config_parts.append(f'  "{key}":"{value}"{separator}')

        config_parts.append("}")

        formatted_config = '\n'.join(config_parts)

        # 保存文件
        with open(output_path, 'w', encoding='utf-8') as f:
            f.write(formatted_config + '\n')

        print(f"\n格式化Map配置文件已生成: {output_path}")
        print(f"\n生成的配置:")
        print(formatted_config)

        return config_map

    except Exception as e:
        print(f"处理过程中出现错误: {e}")
        return {}


# 使用示例
if __name__ == "__main__":
    # Excel文件路径 - 请修改为你的实际文件路径
    excel_file = "D:\\pycharmProject\\pythonProject\\测试-合并结果.xlsx"

    print("=== Excel转Map配置文件工具 ===")

    # 1. 分析数据
    print("1. 分析映射数据...")
    # analyze_mapping_data(excel_file)

    # 2. 生成Map格式配置（单行JSON）
    print("\n2. 生成Map格式配置文件...")
    config_map = excel_to_config_map(excel_file, "test_edition_mapping_config.properties")

    # 3. 生成格式化的Map配置（多行，便于阅读）
    print("\n3. 生成格式化Map配置文件...")
    # formatted_map = excel_to_config_map_formatted(excel_file, "edition_mapping_formatted.properties")


# 快速使用函数
def quick_generate_map_config(excel_file, output_file="mapping_config.properties"):
    """
    快速生成Map格式配置文件
    """
    return excel_to_config_map(excel_file, output_file)